package com.aat.dxfy.jdbc;

import java.sql.*;
import java.util.*;

public class JdbcOpt {
	/**
	 * 
	 * @info Connection DB
	 * @param
	 * @return Connection
	 */
	private static Connection getConnection() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=utf8";
		String user = "";
		String passwd = "";
		try {
			Class.forName(driver);
			return DriverManager.getConnection(url, user, passwd);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("驱动加载错误或者没有找到驱动包!");
			return null;
		}
	}

	/**
	 * @info 字符在字符串中出现的次数
	 * @param string,s
	 * @return int
	 */
	private static int getCountChar(String string, String a) {
		int pos = -2;
		int n = 0;
		while (pos != -1) {
			if (pos == -2) {
				pos = -1;
			}
			pos = string.indexOf(a, pos + 1);
			if (pos != -1) {
				n++;
			}
		}
		return n;
	}

	/**
	 * 
	 * @info 增删改
	 * @param sql,params
	 * @return int
	 */
	public static int addDelUpt(String sql, Object... params) {
		int res = 0;
		int pc = getCountChar(sql, "?");
		if (pc == params.length) {
			Connection con = getConnection();
			try {
				PreparedStatement ps = con.prepareStatement(sql);
				int i = 1;
				for (Object obj : params) {
					if (obj instanceof String) {
						String s = (String) obj;
						ps.setString(i, s);
					} else if (obj instanceof Integer) {
						Integer s = (Integer) obj;
						ps.setInt(i, s);
					} else if (obj instanceof Long) {
						Long s = (Long) obj;
						ps.setLong(i, s);
					} else if (obj instanceof Double) {
						Double s = (Double) obj;
						ps.setDouble(i, s);
					} else if (obj instanceof java.util.Date) {
						java.util.Date s = (java.util.Date) obj;
						java.sql.Timestamp d = new java.sql.Timestamp(s.getTime());
						ps.setTimestamp(i, d);
					}
					i++;
				}

				// System.out.println("sql: " + sql);
				// System.out.println("params: " + Arrays.toString(params));

				res = ps.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					con.commit();
					con.close();
				} catch (SQLException e) {
				}
			}
		}

		return res;
	}

	/**
	 * 
	 * @info 查询
	 * @param
	 * @return
	 */
	public static List<Map<String, Object>> query(String sql, Object... params) {
		int pc = getCountChar(sql, "?");
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if (pc == params.length) {
			Connection con = getConnection();
			try {
				PreparedStatement ps = con.prepareStatement(sql);
				int i = 1;
				for (Object obj : params) {
					if (obj instanceof String) {
						String s = (String) obj;
						ps.setString(i, s);
					} else if (obj instanceof Integer) {
						Integer s = (Integer) obj;
						ps.setInt(i, s);
					} else if (obj instanceof Long) {
						Long s = (Long) obj;
						ps.setLong(i, s);
					} else if (obj instanceof Double) {
						Double s = (Double) obj;
						ps.setDouble(i, s);
					} else if (obj instanceof java.util.Date) {
						java.util.Date s = (java.util.Date) obj;
						java.sql.Timestamp d = new java.sql.Timestamp(s.getTime());
						ps.setTimestamp(i, d);
					}
					i++;
				}

				// System.out.println("sql: " + sql);
				// System.out.println("params: " + Arrays.toString(params));
				ResultSet rs = ps.executeQuery();
				ResultSetMetaData rsa = rs.getMetaData();
				int geshu = rsa.getColumnCount();
				while (rs.next()) {
					Map<String, Object> map = new HashMap<String, Object>();
					for (int j = 1; j <= geshu; j++) {
						String k = rsa.getColumnName(j);
						Object v = rs.getObject(k);
						map.put(k, v);
					}
					list.add(map);
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					con.close();
				} catch (SQLException e) {
				}
			}
		}
		return list;
	}

}
